 /*
 
This program calculates the mean number of countries per US MNE (number in text)

*/


/*

Written By: TFP
Modified on: 3/20/2019 by HR


This program uses the BEA country dataset to calculate manuf affiliate sales for the AFFT sample
*/


 **Set directories
cd ""  /* PROJECT ROOT FOLDER */

/* DEFINE GLOBALS PATHS HERE */
global data data
global temp data/junk
global output output

global code code


*Make some table with US MNEs man aff sales
  *Total should match the summary stats table
  *Shares by type
  *Averages
  
use $data/country_imp_aff2007.dta, clear

*Limit to US MNEs with manuf employment in the US
keep if mne_status=="MNE" 
keep if type=="MAN" | type=="MAN MPRO"



*Limit to firms with man aff sales
tab man_aff foreign, miss
keep if man_aff==1 

*Make country indicator
gen countries=1

*collapse to firm totals
collapse (sum)  man_aff_sales man_aff_sales_oth_fa man_aff_sales_local_unaff ///
  man_aff_sales_local_fa man_aff_sales_oth_unaff man_aff_sales_us_unaff man_aff_sales_us_rep man_aff_sales_ins ///
   countries (mean) sales, by(firmid)
   
gen firms=1  

*Make totals
gen man_aff_sales_local=man_aff_sales_local_unaff+man_aff_sales_local_fa
gen man_aff_sales_other=man_aff_sales_oth_fa + man_aff_sales_oth_unaff

*save dataset for disclosure 
save $temp/disc_temp.dta, replace
     
collapse (mean) countries man_aff_sales man_aff_sales_local  man_aff_sales_other ///
   man_aff_sales_us_unaff man_aff_sales_us_rep man_aff_sales_ins ///
   (sum) man_aff_sales_tot=man_aff_sales firms
   
 
di "Mean number of affiliate countries = " countries

/*
foreach var in man_aff_sales man_aff_sales_local  man_aff_sales_other ///
               man_aff_sales_us_unaff man_aff_sales_us_rep man_aff_sales_ins  {
    replace `var'=round(`var')
    }
*/

do $code/rounding_4sigdig_tf.do  man_aff_sales man_aff_sales_local  man_aff_sales_other ///
                                man_aff_sales_us_unaff man_aff_sales_us_rep

replace countries=round(countries,.01)    

*Results in the NBER WP 
/*
export excel countries man_aff_sales_drb man_aff_sales_local_drb  man_aff_sales_other_drb ///
               man_aff_sales_us_unaff_drb man_aff_sales_us_rep_drb  ///
    using $output/afft_v4.xls, sheetreplace sheet(aff_man_sales) ///
    firstrow(varlabels)	
    */
**************************************************************************************   

